-- MySQL Script generated by MySQL Workbench
-- Thu Jul 11 05:34:15 2019
-- Model: New Model    Version: 1.0
-- MySQL Workbench Forward Engineering

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';

-- -----------------------------------------------------
-- Schema ac_advert
-- -----------------------------------------------------
DROP SCHEMA IF EXISTS `ac_advert` ;

-- -----------------------------------------------------
-- Schema ac_advert
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `ac_advert` DEFAULT CHARACTER SET utf8 ;
USE `ac_advert` ;

-- -----------------------------------------------------
-- Table `ac_advert`.`advert`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`advert` ;

CREATE TABLE IF NOT EXISTS `ac_advert`.`advert` (
  `adv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `msg_type` INT NOT NULL,
  `msg_text` VARCHAR(512) NOT NULL,
  `date_from` DATETIME NULL DEFAULT NULL,
  `date_to` DATETIME NULL DEFAULT NULL,
  `hours` VARCHAR(64) NULL DEFAULT '0-24;',
  `is_vip` TINYINT NULL DEFAULT 0,
  `admin_flags` VARCHAR(64) NULL DEFAULT NULL,
  `views` INT NULL DEFAULT -1,
  `day_of_week` VARCHAR(64) NULL DEFAULT '1-7;',
  `show` TINYINT NULL DEFAULT 1,
  `order` INT NULL DEFAULT 1000,
  PRIMARY KEY (`adv_id`),
  UNIQUE INDEX `adv_id_UNIQUE` (`adv_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ac_advert`.`servers`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`servers` ;

CREATE TABLE IF NOT EXISTS `ac_advert`.`servers` (
  `srv_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `ip` VARCHAR(64) NOT NULL,
  `port` INT NOT NULL,
  `title` VARCHAR(128) NULL DEFAULT 'Server',
  `rcon` VARCHAR(192) NULL,
  `adv_time` FLOAT NULL DEFAULT 45,
  PRIMARY KEY (`srv_id`))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ac_advert`.`magic_words`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`magic_words` ;

CREATE TABLE IF NOT EXISTS `ac_advert`.`magic_words` (
  `word_id` INT NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(64) NOT NULL,
  `value` VARCHAR(256) NOT NULL,
  PRIMARY KEY (`word_id`),
  UNIQUE INDEX `word_id_UNIQUE` (`word_id` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ac_advert`.`hud_style`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`hud_style` ;

CREATE TABLE IF NOT EXISTS `ac_advert`.`hud_style` (
  `adv_id` INT UNSIGNED NOT NULL,
  `color1` VARCHAR(24) NULL DEFAULT '255 255 255 255',
  `color2` VARCHAR(24) NULL DEFAULT '255 255 255 255',
  `effect` INT NULL DEFAULT 1,
  `fadein` FLOAT NULL DEFAULT 0.1,
  `fadeout` FLOAT NULL DEFAULT 0.1,
  `holdtime` FLOAT NULL DEFAULT 10,
  `x` FLOAT NULL DEFAULT 0.5,
  `y` FLOAT NULL DEFAULT 0.5,
  `fxtime` FLOAT NULL DEFAULT 0.1,
  PRIMARY KEY (`adv_id`),
  UNIQUE INDEX `style_id_UNIQUE` (`adv_id` ASC),
  CONSTRAINT `hud_style_adv_id`
    FOREIGN KEY (`adv_id`)
    REFERENCES `ac_advert`.`advert` (`adv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ac_advert`.`server_ads`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`server_ads` ;

CREATE TABLE IF NOT EXISTS `ac_advert`.`server_ads` (
  `srv_id` INT UNSIGNED NOT NULL,
  `adv_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`srv_id`, `adv_id`),
  INDEX `server_ads_adv_id_idx` (`adv_id` ASC),
  CONSTRAINT `server_ads_srv_id`
    FOREIGN KEY (`srv_id`)
    REFERENCES `ac_advert`.`servers` (`srv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `server_ads_adv_id`
    FOREIGN KEY (`adv_id`)
    REFERENCES `ac_advert`.`advert` (`adv_id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

USE `ac_advert` ;

-- -----------------------------------------------------
-- Placeholder table for view `ac_advert`.`ads`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ac_advert`.`ads` (`srv_id` INT, `adv_id` INT, `msg_type` INT, `msg_text` INT, `date_from` INT, `date_to` INT, `hours` INT, `is_vip` INT, `admin_flags` INT, `views` INT, `day_of_week` INT, `show` INT, `order` INT, `color1` INT, `color2` INT, `effect` INT, `fadein` INT, `fadeout` INT, `holdtime` INT, `x` INT, `y` INT, `fxtime` INT);

-- -----------------------------------------------------
-- View `ac_advert`.`ads`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `ac_advert`.`ads`;
DROP VIEW IF EXISTS `ac_advert`.`ads` ;
USE `ac_advert`;
create  OR REPLACE view ads as
SELECT s.srv_id, a.*, h.color1, h.color2, h.effect, h.fadein, h.fadeout, h.holdtime, h.x, h.y, h.fxtime FROM server_ads as s join advert as a using(adv_id) left join hud_style as h using(adv_id) where a.show = 1 order by a.`order`;

SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
